﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;

public class DatabaseHelper : IDisposable
{
    private SQLiteConnection connection;

    /// <summary>
    /// 构造函数，用于初始化SQLiteConnection并打开连接
    /// </summary>
    /// <param name="connectionString">SQLite数据库连接字符串</param>
    public DatabaseHelper(string connectionString)
    {
        connection = new SQLiteConnection(connectionString);
        connection.Open();
    }

    /// <summary>
    /// 获取所有表名
    /// </summary>
    /// <returns>表名列表</returns>
    public List<string> GetAllTableNames()
    {
        List<string> tableNames = new List<string>();
        string query = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;";
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            using (SQLiteDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    tableNames.Add(reader.GetString(0));
                }
            }
        }
        return tableNames;
    }

    /// <summary>
    /// 获取指定表的所有列名
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <returns>列名列表</returns>
    public List<string> GetAllColumnNames(string tableName)
    {
        List<string> columnNames = new List<string>();
        string query = $"PRAGMA table_info({tableName})";
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            using (SQLiteDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    columnNames.Add(reader.GetString(1));
                }
            }
        }
        return columnNames;
    }

    /// <summary>
    /// 获取指定表的指定列的所有值
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="columnName">列名</param>
    /// <returns>值列表</returns>
    public List<string> GetDataByColumnName(string tableName, string columnName)
    {
        List<string> data = new List<string>();
        string query = $"SELECT {columnName} FROM {tableName}";
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            using (SQLiteDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    data.Add(reader.GetString(0));
                }
            }
        }
        return data;
    }

    /// <summary>
    /// 向指定表的指定列写入数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="columnName">列名</param>
    /// <param name="value">数据值</param>
    public void WriteData(string tableName, string columnName, string value)
    {
        string query = $"INSERT INTO {tableName} ({columnName}) VALUES ('{value}')";
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            command.ExecuteNonQuery();
        }
    }
    /// <summary>
    /// 向SQLite数据库中写入一行数据
    /// </summary>
    /// <param name="tableName">要写入的表名</param>
    /// <param name="data">要写入的数据，以Dictionary<string, object>类型表示，其中键为列名，值为对应的数据</param>
    public void WriteData(string tableName, Dictionary<string, object> data)
    {
        string columns = string.Join(", ", data.Keys);
        string values = string.Join(", ", data.Values.Select(v => $"'{v}'"));
        string query = $"INSERT INTO {tableName} ({columns}) VALUES ({values})";
        using (SQLiteCommand command = new SQLiteCommand(query, connection))
        {
            command.ExecuteNonQuery();
        }
    }

    public void Dispose()
    {
        connection.Close();
        connection.Dispose();
    }

    /// <summary>
    /// 析构函数，用于关闭数据库连接
    /// </summary>
    //~DatabaseHelper()
    //{
    //    connection.Close();
    //}
}